package com.ejoysoft.wlanyz.business;

import java.util.Vector;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashSet;

import com.ejoysoft.common.DbConnect;
import com.ejoysoft.common.Globa;

public class PoorcoverageAP {
	private Globa globa;
    private DbConnect db;


    public PoorcoverageAP() {
    }

    public PoorcoverageAP(Globa globa) {
        this.globa = globa;
        db = globa.db;
    }

    public PoorcoverageAP(Globa globa, boolean b) {
        this.globa = globa;
        db = globa.db;
        if (b) globa.setDynamicProperty(this);
    }

    String strTableName1= "ac_class_associate";
    String strTableName2 = "rm_device";
    String strTableName4 = "nhm_channel_change";
    
   
    

    //记录集转化为对象
    public PoorcoverageAP load(ResultSet rs, boolean isView,String strDegree,String strStartTime,String strEndTime) {
        PoorcoverageAP theBean = new PoorcoverageAP();
        DbConnect dbConnect = new DbConnect();
         
        try {
        	String strTableName3;
       	   if(strDegree.equals("小时"))
       		   strTableName3 = "nhm_ap_rssi_kpi_hour";
       	   else strTableName3 = "nhm_ap_rssi_kpi_day";
       	    theBean.setstrId(rs.getString(1));//
        	theBean.setstrCity(rs.getString(2));//城市
            theBean.setstrProper(rs.getString(3));//区县
            if(rs.getString(4)==null)theBean.setAPName("无AP");
            else theBean.setAPName(rs.getString(4)); //ap名
            

           
            String sql1 = "select b.device_id  from "+strTableName1+" a left join ( select * from "+strTableName2+" where device_id = '" + rs.getString(5) + "' and device_name = '"+theBean.getAPName()+"' ) b on b.hotspot=a.classtwoac";
            String sql2 = "select avg(rsn),avg(rssi) from ("+sql1+")  t1,"+strTableName3+" t2  where 1=1 ";
            String sql3=" and t1.device_id=t2.device_id ";
        	//String sql0=" group by "
        if(strStartTime.length()>0&&strEndTime.length()>0)
  		 {
  			 strStartTime="to_date('" + strStartTime + "','dd-MM-yy hh24.mi.ss') ";
  			 strEndTime="to_date('" + strEndTime + "','dd-MM-yy hh24.mi.ss') ";
  			 sql2 = sql2.concat(" and t2.timestamp>=" + strStartTime).concat(" and t2.timestamp<=" + strEndTime).concat(sql3);
  		 }
          
  		 else if(strStartTime.length()>0&&!(strEndTime.length()>0))
  		 {
  			 strStartTime="to_date('" + strStartTime + "','dd-MM-yy hh24.mi.ss') ";
          	sql2 = sql2.concat(" and t2.timestamp>=" + strStartTime ).concat(sql3);
  		 }
  		 
          else if(!(strStartTime.length()>0)&&strEndTime.length()>0)
          {
          	strEndTime="to_date('" + strEndTime + "','dd-MM-yy hh24.mi.ss') ";
          	sql2 = sql2.concat(" and t2.timestamp<=" + strEndTime).concat(sql3);
          }
          else sql2 =sql2.concat(sql3);
        //System.out.println("***************8"+sql2);
        ResultSet rs1 = dbConnect.executeQuery(sql2);
        if(rs1.next())
        { 
        double rsn = rs1.getDouble(1);
        BigDecimal b = new BigDecimal(rsn);
        rsn = b.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();//保留两位小数
        theBean.setnoiseRatio(rsn);
      	double rssi = rs1.getDouble(2);
      	BigDecimal c = new BigDecimal(rssi);
      	rssi = c.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();//保留两位小数
      	theBean.setfieldIntens(rssi);//场强
        }
        rs1.close();
        String sql4 = " select avg(quality)  from ("+sql1+")t1,"+strTableName4+" t2 where 1=1 ";
        sql3=" and t1.device_id=t2.device_id ";
        if(strStartTime.length()>0&&strEndTime.length()>0)
 		 {
 			  sql4 = sql4.concat(" and t2.timestamp>=" + strStartTime).concat(" and t2.timestamp<=" + strEndTime).concat(sql3);
 		 }
         
 		 else if(strStartTime.length()>0&&!(strEndTime.length()>0))
 		 {
 			 	sql4 = sql4.concat(" and t2.timestamp>=" + strStartTime ).concat(sql3);
 		 }
 		 
         else if(!(strStartTime.length()>0)&&strEndTime.length()>0)
         {
         	sql4 = sql4.concat(" and t2.timestamp<=" + strEndTime).concat(sql3);
         }
         else sql4 =sql4.concat(sql3);
        ResultSet rs2 = dbConnect.executeQuery(sql4);
        if(rs2.next())
        {
        
      	 double quality = rs2.getDouble(1);
       	 BigDecimal d = new BigDecimal(quality);
       	 quality = d.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();//保留两位小数
       	theBean.setrouteQuality(quality);//信道质量
        }
          rs2.close();

        } catch (Exception e) {
            e.printStackTrace();
            //System.out.println("bbbbbbbbbb"+e);
          
        }
        dbConnect.closeCon();
        return theBean;
    }
   
    //调出所有热点的城市名称
    public ArrayList<Object[]> getAllCitiesByName( String where )
    {
    	ArrayList<Object[]>  beans = new ArrayList<Object[]>();
    	String sql = "select distinct(strCity) from "+strTableName1+" ";
    	if(where!=null && where.length()>0)
    		sql = sql +" "+where;
    	ResultSet re = db.executeQuery(sql);
    	try {
			if(re != null && re.next())
			{
				do{
					Object[] theBean = new Object[2];
					String city = re.getString("strCity");
					theBean[0] = city;
					theBean[1] = this.getAllstrcountryByName(city);
					beans.add(theBean);
				}while(re.next());
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return beans;
    }
    //调出城市所有区县名称
    public ArrayList<String> getAllstrcountryByName( String city)
    {
    	ArrayList<String>  beans = new ArrayList<String>();
    	String sql = "select distinct(strcountry) from "+strTableName1+" ";
    	String where = " where strCity='"+city+"'";
    	sql = sql+where;
    	ResultSet re = db.executeQuery(sql);
    	try { 
			if(re != null && re.next())
			{   //String strcountry = re.getString("");
				do{
					String strcountry = re.getString("strcountry");
					beans.add(strcountry);
				}while(re.next());
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return beans;
    }
  
  //查询符合条件的记录总数
    public int getCount( String where,String HotSpot,String HotSpot2) {
         int count = 0;
         try {
        	
        	 
        		 String sql= "  select count(distinct device_id)from "+strTableName1+ " a,"+strTableName2+" b ";
        		 if (where.length() > 0) {
                	 where = where.toLowerCase();
                	 if (where.indexOf("order") > 0)
                		 where = where.substring(0, where.lastIndexOf("order"));
                	 sql = String.valueOf(sql) + String.valueOf(where);
                	 }
        		 sql+=" and classtwoac=hotspot and hotspot='"+HotSpot2+"' and classoneac='"+HotSpot+"' ";
        		 
            // System.out.println("11111111111111111111"+sql);
            	 
                 ResultSet rs = db.executeQuery(sql);
                 if (rs.next())
                	 count = rs.getInt(1);
                 
                 rs.close();
                 return count;
                 
        	 
        	 } 
         catch (Exception ee) {
        	 ee.printStackTrace();
             return count;
             }
         }
//列表记录集
    public Vector<PoorcoverageAP> list(String strDegree,String where,int startRow, int rowCount,String strStartTime,String strEndTime,String HotSpot,String HotSpot2) {
    	Vector<PoorcoverageAP> beans = new Vector<PoorcoverageAP>();
     	//Vector<Poorcoverage> vector = new Vector<Poorcoverage>();
    	 try {
    		 
    		 String sql = " select  strid,strcity,strcountry,device_name,b.device_id from (select * from "+strTableName1+" where classoneac='"+HotSpot+"' and classtwoac = '"+HotSpot2+"' )a left join "+
    		 strTableName2+" b on a.classtwoac = b.hotspot ";
        	 //String sql= "  select strcity,strcountry,device_name,b.device_id  from "
    			// +strTableName1+" a ,"+strTableName2+" b ";
    		 if (where.length() > 0) {
            	 where = where.toLowerCase();
            	 if (where.indexOf("order") > 0)
            		 where = where.substring(0, where.lastIndexOf("order"));
            	 sql = String.valueOf(sql) + String.valueOf(where);
            	 }
    		// sql+=" and classtwoac=hotspot and hotspot='"+HotSpot2+"' and classoneac='"+HotSpot+"' ";
                  
        		 
                        
                    
                        Statement s = db.getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);//链接数据库
                        
                        if (startRow != 0 && rowCount != 0)
                            s.setMaxRows((startRow + rowCount) - 1);
                       // System.out.println("3333333333333333333"+sql);
                   	 
                        ResultSet rs = s.executeQuery(sql);
                        
                        if (rs != null && rs.next()) {
                        	if (startRow != 0 && rowCount != 0)
                                rs.absolute(startRow);
                        	do {
                                PoorcoverageAP theBean = new PoorcoverageAP();
                                theBean = load(rs, false,strDegree,strStartTime,strEndTime);
                                beans.addElement(theBean);
                                } while (rs.next());
                        	}
                        
                        rs.close();
                        s.close();
    		       
    		        } 
    	 
    	 catch (Exception ee) {
    		 ee.printStackTrace();
    		 }
    	 return beans;
     }
	public String returnCityName(String fontString)
	{
	return null;
	}
	
    private String strId;//	        id号
    private String strCity;//		城市
    private String strProper;//     区县
     private String APName;//	     AP名
    private String poorCover;//		是否弱覆盖
    private int    userNumber;//	用户数
    private double noiseRatio ;//	信噪比
    private double fieldIntens;//	场强
    private double routeQuality;//  信道质量
   

    public String getstrId() {
        return strId;
    }

    public void setstrId(String strId) {
        this.strId = strId;
    }
    
   public String getstrCity() {
        return strCity;
    }

    public void setstrCity(String strCity) {
        this.strCity = strCity;
    }
    public String getstrProper() {
        return strProper;
    }

    public void setstrProper(String strProper) {
        this.strProper = strProper;
    }
  
    public void setAPName(String APName){
    	 this.APName = APName;
    }
    
    public String getAPName() {
		return APName;
	}
    
  
    public String getpoorCover() {
		return poorCover;
	}
    
  
    public int getuserNumber(){
    	return userNumber;
    }
    
    public void setuserNumber(int userNumber){
    	this.userNumber=userNumber;
    }
 
    public Number getnoiseRatio () {
        return noiseRatio ;
    }

    public void setnoiseRatio (double noiseRatio ) {
    	this.noiseRatio  = noiseRatio ;
        
    }   

    public double getfieldIntens () {
        return fieldIntens;
    }

    public void setfieldIntens(double fieldIntens) {
    	
        this.fieldIntens = fieldIntens;
        if(fieldIntens<-70)this.poorCover = "弱覆盖";//判断是否弱覆盖
		else this.poorCover = "正常";
    }
    public double getfrouteQuality() {
        return routeQuality;
    }

    public void setrouteQuality(double routeQuality) {
    	
        this.routeQuality = routeQuality;
    }
  
 
}
